if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_PersonnelDept_Update]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_PersonnelDept_Update]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.sp_PersonnelDept_Update
(
@UserName	varchar(20) = NULL, 
@Org 	varchar(32) = NULL
)
AS

BEGIN TRANSACTION

IF NOT EXISTS(select 1 from DepartmentXref WHERE  DeptCode = @Org)
   BEGIN 
	SET @Org = '0100'
   END 

	UPDATE PersonnelXref 
	SET DeptCode = @Org
	WHERE LoginID = @UserName

IF EXISTS (SELECT 1 FROM PersonnelDeptXref WHERE PersonID = @UserName)
   BEGIN
	UPDATE PersonnelDeptXref
	SET DeptCode = @Org,
	    PrimaryDept = 1
	WHERE PersonID = @UserName

   END
ELSE
   BEGIN
	INSERT INTO PersonnelDeptXref 
		(PersonID, DeptCode, PrimaryDept)
	VALUES (@UserName, @Org, 1)
   END

COMMIT TRANSACTION

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

